package dane.brown.sql.checker.service.impl;

import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvException;
import dane.brown.sql.checker.MyBatisOracleSyntaxChecker;
import dane.brown.sql.checker.service.CheckPrinter;
import dane.brown.sql.checker.service.MyBatisSyntaxCheckerCore;
import dane.brown.sql.checker.service.model.CheckRule;
import dane.brown.sql.checker.service.model.PrintRow;
import lombok.extern.log4j.Log4j2;
import org.apache.ibatis.builder.BuilderException;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.ognl.OgnlException;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeAliasRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
@Service
@Log4j2
public class MyBatisSyntaxCheckerCoreImpl implements MyBatisSyntaxCheckerCore {
    @Autowired
    private List<CheckRule> checkRules;
    // 检查规则：Oracle特有语法正则 + 错误提示 + MySQL替代方案
    private static final List<CheckRule> CHECK_RULES = new ArrayList<>();

    @Autowired
    private CheckPrinter csvPrinter;


    // 匹配MyBatis-Plus的ew表达式（如${ew.customSqlSegment}）
    private static final Pattern EW_EXPRESSION_PATTERN = Pattern.compile("\\$\\{ew\\.\\w+\\}");
    // 匹配所有${}表达式
    private static final Pattern DOLLAR_EXPRESSION_PATTERN = Pattern.compile("\\$\\{[^}]+\\}");
    // 匹配所有#{}表达式
    private static final Pattern HASH_EXPRESSION_PATTERN = Pattern.compile("#\\{[^}]+\\}");
    // 匹配XML标签
    private static final Pattern XML_TAG_PATTERN = Pattern.compile("<[^>]+>", Pattern.DOTALL);
    //必须匹配mapper文件，因为有可能会遇到其他格式的xml。
    private static final Pattern MYBATIS_MAPPER_PATTERN = Pattern.compile("<mapper\\s+", Pattern.CASE_INSENSITIVE);
    // 用于过滤编译目录的正则（如target/、build/等）
    private static final Pattern BUILD_DIR_PATTERN = Pattern.compile("(target|build|out)/", Pattern.CASE_INSENSITIVE);
    // 初始化MyBatis配置（跳过类验证）
    private Configuration configuration = new Configuration() {
        @Override
        public TypeAliasRegistry getTypeAliasRegistry() {
            return new TypeAliasRegistry() {
                @Override
                public <T> Class<T> resolveAlias(String alias) {
                    try {
                        return super.resolveAlias(alias);
                    } catch (Exception e) {
                        return (Class<T>) Object.class;
                    }
                }
            };
        }

    };

    @PostConstruct
    public void init(){
        if(checkRules!= null && !checkRules.isEmpty()) {
            CHECK_RULES.addAll(checkRules);
        }
    }

    /**
     * 递归处理文件
     * @param directoryName
     */
    private void listFiles(String directoryName) {
        File directory = new File(directoryName);
        if (directory.isDirectory()) {
            File[] files = directory.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isFile() && file.getName().endsWith(".csv")) {
                        processCsvFile(file.getAbsolutePath());
                    } else if (file.isDirectory()) {
                        listFiles(file.getAbsolutePath());
                    }
                }
            }
        }
    }

    /**
     * 处理csv规则
     * @param configPath
     * @return
     */
    private boolean  processCsvFile(String configPath){
        try (CSVReader csvReader = new CSVReader(new InputStreamReader(
                new FileInputStream(configPath), StandardCharsets.UTF_8))) {
            String[] header = csvReader.readNext(); // 读取并忽略标题行
            if (header == null) {
                System.err.println("CSV文件没有标题行，无法解析规则");
                return false;
            }
            int i = 0;
            String[] line;
            while ((line = csvReader.readNext()) != null) {
                if (line.length != 3) {
                    System.err.println("规则配置不完整，已跳过");
                    continue;
                }

                String regex = line[0];
                String msg = line[1];
                String suggestion = line[2];

                CheckRule rule = new CheckRule(regex, msg, suggestion);
                if(log.isDebugEnabled()){
                    log.debug("{}",rule);
                }
                CHECK_RULES.add(rule);
                i++;
            }
            System.out.println("成功加载 " + i + " 条检查规则");
        } catch (IOException | CsvException e) {
            log.error("读取配置文件失败：" + e.getMessage(),e);
            return false;
        }finally{

            System.out.println("成功加载 " + CHECK_RULES.size() + " 条检查规则");
        }
        return true;
    }

    /**
     * 加载csv配置文件
     * @param configPath
     * @return
     */
    @Override
    public boolean loadCheckRules(String configPath) {

        File configFile = new File(configPath);
        log.debug("成功读取配置文件:{}",configFile.getAbsolutePath());
        if (!configFile.exists()) {
            System.err.println("配置文件不存在：" + configPath);
            return false;
        }
        if(configFile.isDirectory()){
            listFiles(configPath);
            return true;
        }else if(configFile.isFile()){
            return processCsvFile(configPath);
        }

        return true;

    }
    @Override
    public void traverseAndParseXmlFiles(File dir) {
        // 用于记录已解析的文件路径，防止重复解析
        Set<String> parsedFiles = new HashSet<>();

        traverseFiles(dir, configuration, parsedFiles);
    }

    @Override
    public void checkMappedStatements(String baseDir) {
        // 修正：getMappedStatements()返回的是Collection而非Map
        List<MappedStatement> msCollection = configuration.getMappedStatements().stream().distinct().collect(Collectors.toList());

        for (int i = 0; i < msCollection.size(); i++) {
            if (msCollection.get(i) instanceof MappedStatement) {
                try {
                    MappedStatement ms = msCollection.get(i);
                    String msId = ms.getId(); // 获取SQL语句的唯一标识（namespace.id）

                    // 获取SQL内容（处理各种表达式错误）
                    String sql = getSqlWithFallback(ms, baseDir);
                    if (sql == null || sql.trim().isEmpty()) continue;

                    // 定位原始文件
                    String resource = ms.getResource();
                    File xmlFile = new File(resource);
                    if (!xmlFile.exists()) {
                        xmlFile = new File(baseDir, resource.replace("/", File.separator));
                    }

                    // 检查SQL中的Oracle语法
                    checkSqlContent(sql, msId, xmlFile);
                } catch (Exception e) {
                    System.err.println(e.getMessage());
                }
            }
        }
        csvPrinter.flush();
    }
    private static String getSqlWithFallback(MappedStatement ms, String baseDir) {
        try {
            // 尝试正常获取SQL，使用空参数映射
            BoundSql boundSql = ms.getBoundSql(new HashMap<>());
            return boundSql.getSql();
        } catch (Exception e) {
            // 处理所有表达式求值错误
            if (e.getMessage().contains("evaluated to a null value") ||
                    e.getMessage().contains("ew.") ||
                    e.getCause() instanceof OgnlException) {
                log.debug("检测到表达式求值错误（" + ms.getId() + "），使用原始XML内容进行检查");
                // 从原始XML文件中提取SQL片段作为备选
                return extractSqlFromXml(ms.getResource(), baseDir);
            } else {
                log.error("获取SQL失败（" + ms.getId() + "）：" + e.getMessage());
                return null;
            }
        }
    }
    /**
     * 检查SQL内容中的Oracle语法
     */
    private void checkSqlContent(String sql, String msId, File xmlFile) {
        boolean hasIssue = false;
        List<String> lines = null;
        String fileContent = null;

        try {
            if (xmlFile.exists()) {
                lines = Files.readAllLines(Paths.get(xmlFile.getPath()), StandardCharsets.UTF_8);
                fileContent = String.join("\n", lines);
            }
        } catch (IOException e) {
            // 忽略读取失败
        }

//        System.out.println("\n检查SQL：" + msId);
//        System.out.println("文件：" + (xmlFile.exists() ? xmlFile.getAbsolutePath() : "未知路径"));

        for (CheckRule rule : CHECK_RULES) {
            Matcher matcher = rule.getPattern().matcher(sql);
            while (matcher.find()) {
                hasIssue = true;
                String matchedText = matcher.group();
                int lineNum = -1;

                if (fileContent != null) {
                    lineNum = getLineNumber(fileContent, matchedText);
                }
                PrintRow row = PrintRow.builder()
                        .msId(msId)
                        .xmlPath(xmlFile.exists() ? xmlFile.getAbsolutePath() : "未知路径")
                        .matchedText(matchedText)
                        .lineNumber(String.valueOf(lineNum))
                        .errorMsg(rule.getErrorMsg())
                        .suggestion(rule.getSuggestion())
                        .build();
                csvPrinter.print(row);
                System.out.println("\n检查SQL：" + msId);
                System.out.println("文件：" + (xmlFile.exists() ? xmlFile.getAbsolutePath() : "未知路径"));
                System.out.printf("  发现：%s%n", rule.getErrorMsg());
                System.out.printf("  匹配内容：%s%n", matchedText);
                System.out.printf("  行号（参考）：%d%n", lineNum);
                System.out.printf("  建议：%s%n%n", rule.getSuggestion());
            }
        }

        if (!hasIssue) {
            log.debug("  未发现Oracle特有语法");
        }
    }

    /**
     * 计算匹配内容在文件中的行号
     */
    private static int getLineNumber(String fileContent, String matchedText) {
        int index = fileContent.indexOf(matchedText);
        if (index == -1) {
            // 如果精确匹配失败，尝试模糊匹配
            String[] parts = matchedText.split("\\s+");
            for (String part : parts) {
                if (part.length() > 3) { // 只匹配有意义的片段
                    index = fileContent.indexOf(part);
                    if (index != -1) break;
                }
            }
        }
        if (index == -1) return -1;

        int lineNum = 1;
        for (int i = 0; i < index; i++) {
            if (fileContent.charAt(i) == '\n') {
                lineNum++;
            }
        }
        return lineNum;
    }
    /**
     * 从XML文件中直接提取SQL内容
     */
    private static String extractSqlFromXml(String resource, String baseDir) {
        try {
            File xmlFile = new File(resource);
            if (!xmlFile.exists()) {
                xmlFile = new File(baseDir, resource.replace("/", File.separator));
            }

            if (!xmlFile.exists()) {
                return null;
            }

            // 读取XML文件内容
            String xmlContent = new String(Files.readAllBytes(Paths.get(xmlFile.getPath())), StandardCharsets.UTF_8);

            // 1. 移除注释
            String noComments = xmlContent.replaceAll("<!--[\\s\\S]*?-->", "");

            // 2. 移除XML标签
            String noTags = XML_TAG_PATTERN.matcher(noComments).replaceAll(" ");

            // 3. 移除所有${}和#{}表达式
            String noDollarExp = DOLLAR_EXPRESSION_PATTERN.matcher(noTags).replaceAll("");
            String noHashExp = HASH_EXPRESSION_PATTERN.matcher(noDollarExp).replaceAll("");

            // 4. 清理多余空格
            return noHashExp.replaceAll("\\s+", " ").trim();
        } catch (Exception e) {
            log.error("提取XML原始SQL失败：" + e.getMessage(),e);
            return null;
        }
    }
    /**
     * 遍历目录并解析XML文件
     */
    private void traverseFiles(File dir, Configuration configuration,Set<String> parsedFiles) {
        File[] files = dir.listFiles();
        if (files == null) return;

        for (File file : files) {
            if (file.isDirectory()) {
                traverseFiles(file, configuration,parsedFiles);
            } else if (file.getName().toLowerCase().endsWith(".xml")) {
                String filePath = file.getAbsolutePath().replace("\\","/");
                // 过滤编译目录（如target/、build/等）
                if (BUILD_DIR_PATTERN.matcher(filePath).find()) {
                    log.debug("跳过编译目录文件：" + filePath);
                    continue;
                }
                if (!isMyBatisMapperFile(file)) {
                    log.debug("跳过非MyBatis XML文件：" + file.getAbsolutePath());
                    continue;
                }
                // 检查是否已解析过该文件
                if (parsedFiles.contains(filePath)) {
                    log.debug("跳过重复解析的文件：" + filePath);
                    continue;
                }
                parsedFiles.add(filePath);
                try (InputStream is = new FileInputStream(file)) {
                    String resource = file.getAbsolutePath().replace("\\", "/");
                    XMLMapperBuilder mapperBuilder = new XMLMapperBuilder(
                            is, configuration, resource, configuration.getSqlFragments()
                    );
                    try {
                        mapperBuilder.parse();
                        log.debug("已解析文件：" + file.getAbsolutePath());
                    } catch (BuilderException e) {
                        if (e.getMessage().contains("ClassNotFoundException") ||
                                e.getMessage().contains("Could not resolve type alias")) {
                            log.debug("已跳过类验证解析文件：" + file.getAbsolutePath());
                        } else {
                            log.error("解析文件出错：" + file.getAbsolutePath() + "，错误：" + e.getMessage());
                        }
                    }
                } catch (Exception e) {
                    log.error("读取文件失败：" + file.getAbsolutePath() + "，错误：" + e.getMessage());
                }
            }
        }
    }
    /**
     * 判断是否为MyBatis映射文件（包含<mapper>标签）
     */
    private boolean isMyBatisMapperFile(File xmlFile) {
        try {
            // 读取文件前1000字节判断（无需读取整个文件，提高效率）
            byte[] buffer = new byte[1000];
            try (InputStream is = new FileInputStream(xmlFile)) {
                int bytesRead = is.read(buffer);
                if (bytesRead > 0) {
                    String content = new String(buffer, 0, bytesRead, StandardCharsets.UTF_8);
                    return MYBATIS_MAPPER_PATTERN.matcher(content).find();
                }
            }
            return false;
        } catch (IOException e) {
            System.err.println("判断文件类型失败：" + xmlFile.getAbsolutePath() + "，错误：" + e.getMessage());
            return false;
        }
    }


}
